import xlrd
def getTotalNum(sheet_names,sheet):
    rows = sheet.nrows  # 获取工作表格的行数
    cols = sheet.ncols  # 获取工作表格的列数
    print('表格 "{}" 总共有 {} 行，{} 列'.format(sheet_names[0], rows, cols))
def  getPhoneNumByMobileOperator():
    phoneList = sheet.col_values(5)
    Telecom =0
    Mobile =0
    Unicom =0
    TelecomList=["134","135","136","137","138","139","147","150","151","152","157","158","159","178","182","183","184","187","188","1703","1705","1706"]
    MobileList=["130","131","132","145","155","156","175","176","185","186","1704","1707","1708","1709","171"]
    UnicomList=["133","149","153","173","177","180","181","189","1700","1701","1702"]
    for i in phoneList:
        i=str(i)
        for t in TelecomList:
            t=str(t)
            if i.startswith(t):
                Telecom+=1
                break
        for m in MobileList:
            m=str(m)
            if i.startswith(m):
                Mobile+=1
                break
        for u in UnicomList:
            u=str(u)
            if i.startswith(u):
                Unicom+=1
                break
    total=Telecom+Mobile+Unicom
    print("办电信，联通，移动的用户数量并计算出三种用户的占比",end=",")
    print("电信占比%s %%，联通占比%s %%，移动占比%s %%"%(round((Telecom/total)*100,2),round((Mobile/total)*100,2),round((Unicom/total)*100,2)))
def getBoyNum():
    boy = 0
    girl = 0
    sexlist = sheet.col_values(8)
    for i in sexlist:
        if i == "男":
            boy += 1
        elif i == "女":
            girl += 1
    print("男生人数:%s  女生人数:%s" % (boy, girl))
def getOldAgeNum():
    ageList = sheet.col_values(7)
    oldNum = 0
    for i in ageList:
        if i == "年龄": continue
        if int(i) >= 45:
            oldNum += 1
    print("老员工人数:%s" % (oldNum))

def  getMoneyLevelNum():
    great8000 = 0
    lower3000 = 0
    MoneyList = sheet.col_values(11)
    for i in MoneyList:
        if i == "薪资": continue
        if i > 8000:
            great8000 += 1
        elif i < 3000:
            lower3000 += 1
    print("薪资高于8000元的高薪人员数量:%s 薪资低于3000的底薪人员数量:%s" % (great8000, lower3000))
def getCompanyNumByMediaNum():
    companyList = sheet.col_values(13)
    mediaNum = 0
    for i in companyList:
        i=str(i)
        if i == "外包公司": continue
        if i.__contains__("传媒"):
            mediaNum+=1
    print("去传媒公司的工作的人员数量:%s" % mediaNum)
def getNumByCount():
    companyList = sheet.col_values(9)
    heilongjiang=0
    beijing=0
    fujian=0
    sichuang=0
    for i in companyList:
        i = str(i)
        if i == "居住地址": continue
        if i.__contains__("黑龙江"):
            heilongjiang += 1
        elif i.__contains__("北京"):
            beijing+=1
        elif i.__contains__("福建"):
            fujian+=1
        elif i.__contains__("四川"):
            sichuang+=1
    totalNum=heilongjiang+beijing+fujian+sichuang
    print("可能在疫情高危地区的人数:%s" % totalNum)
if __name__ == '__main__':
    xls = xlrd.open_workbook("C:\\Users\\15420\\Desktop\\python自动化\第八天\\5.baidu-员工的人员信息.xls")
    sheet_names = xls.sheet_names()  # 获取所有 sheet 表格的名称
    sheet = xls.sheets()[0]  # 获取表格 sheets 对象
    getTotalNum(sheet_names,sheet)
    getPhoneNumByMobileOperator()
    getBoyNum()
    getOldAgeNum()
    getMoneyLevelNum()
    getCompanyNumByMediaNum()
    getNumByCount()
